import datetime
import os
import time
import pandas as pd
from flask import Blueprint, request, send_file
from flask import current_app

from utilsapp.mysql_con import db_connect, db_pagelist, db_update, db_list
from utilsapp.mysql_con import (
    db_operation,
    db_fetchone,
    db_insert,
)
from utilsapp.common import parsing_permissions, get_url_user
from utilsapp.common import assign_value, parsing_data_id, find_str
from utilsapp import utils
import requests
import json

warn = Blueprint("warn", __name__)


# 查询告警列表
@warn.route("/warnlist", methods=["post"])
def get_warn_list():
    a = request.json
    page = assign_value(a, "page")
    size = assign_value(a, "size", 10)
    sql = f"""SELECT * from warn where is_show = 1 and notice = 0 order by pred_time desc """
    res = db_pagelist(sql, page, size)
    return res


@warn.route("/allList", methods=["post"])
def allList():
    a = request.json
    b = parsing_permissions(request)
    scope_id = assign_value(a, "scope_id", 0)
    scoped = parsing_data_id(scope_id, request)
    sql = f"SELECT * from warn where is_show = 1 and {scoped} order by pred_time desc"
    res = db_pagelist(sql, a["page"], a["size"])
    return res


# 删除告警
@warn.route("/delete")
def warn_delete():
    id = int(request.args.get("id"))
    sql = f"""update warn set is_show=0 where id = {id}"""
    res = db_operation(sql)
    affected_rows = res["Affected_rows"]
    if affected_rows >= 1:
        return {"code": 0, "msg": "删除成功", "data": res}
    else:
        return {"code": 1, "msg": "删除失败"}


# 创建工单
@warn.route("/create/notify", methods=["post"])
def notify_create():
    user_id = request.json.get("user_id")
    accept_id = request.json.get("accept_id")
    warn_id = request.json.get("warn_id")
    title = request.json.get("title")
    content = request.json.get("content")
    warn_query = f"""select * from warn where id = {warn_id}"""
    warn_res = db_connect(warn_query)["data"][0]
    notify_info = (
        title,
        content,
        user_id,
        accept_id,
        warn_res["type_id"],
        warn_res["warn_source"],
        warn_res["warn_level"],
        str(warn_res["create_time"]),
        warn_res["group_id"],
        warn_res["company_id"],
        warn_res["factory_id"],
        warn_res["zone_id"],
        warn_res["device_id"],
    )
    notify_insert = f"""insert into notify (`title`, `content`, `user_id`, `accept_id`, `type_id`, `warn_source`, 
                    `warn_level`, `warn_time`, `group_id`, `company_id`, `factory_id`, `zone_id`, 
                    `device_id`) VALUES {notify_info}"""
    res = db_operation(notify_insert)
    affected_rows = res["Affected_rows"]
    # 生产工单后将 notice 设置 为1
    warn_change = f"""update warn set notice=1 where id ={warn_id}"""

    res2 = db_operation(warn_change)
    if affected_rows >= 1:
        return {"code": 0, "msg": "添加成功", "data": res}
    else:
        return {
            "code": 1,
            "msg": "添加失败",
        }


# 获取工单推送列表
@warn.route("/pull/list", methods=["post"])
def pulllist_get():
    warn_id = request.json.get("warn_id")
    # flag = parsing_permissions(request)  # 获取用户权限
    zone_query = f""" select zone_id from warn where id = {warn_id}"""
    zone_id = db_connect(zone_query)["data"][0]["zone_id"]
    role_query = f"""select id from role where dataId = {zone_id}"""

    role_res = db_connect(role_query)["data"]
    if not role_res:
        return {"code": 1, "msg": "查询失败"}
    else:
        role_ids = [i["id"] for i in role_res]
        if len(role_res) == 1:
            whe = f"""role_id = {role_ids[0]}"""
        else:
            whe = f"""role_id in {tuple(role_ids)}"""
        user_query = f"""select id,user_name,nick_name from userinfo where  {whe}"""
        return {"code": 0, "msg": "查询成功", "data": db_connect(user_query)["data"]}


@warn.route("/download", methods=["post"])
def aa():
    date = time.strftime("%Y%m%d", time.localtime())
    scoped_id = assign_value(request.json, "scoped_id")
    sql = f"SELECT name from regionlevel where id = { scoped_id}"
    scoped_name = db_connect(sql)["data"][0]["name"]
    filename = f"{scoped_name}-告警列表.xlsx"
    scoped = parsing_data_id(scoped_id, request)
    query = ""
    if scoped != "":
        query = f" and w.{scoped}"
    sql = f"""SELECT w.*,s.sensor_name,d.device_name from warn w 
    LEFT JOIN sensor s on s.id=w.sensor_id
    LEFT JOIN device d on d.id=w.device_id
    where w.is_show=1 {query}"""
    data = db_list(sql)["data"]
    res_ = []
    mapping_warn_level = {1: "一般", 2: "中等", 3: "严重"}
    mapping_warn_source = {
        "1": "阈值告警",
        "2": "动态阈值",
        "3": "趋势告警",
        "4": "AI模型",
        "5": "设备机理",
    }
    mapping_notice = {0: "未生成工单", -1: "已忽略", 1: "已生成工单", 2: "工单已处理"}
    for i in data:
        warn_level = mapping_warn_level.get(i["warn_level"], "")
        warn_source = mapping_warn_source.get(i["warn_source"], "")
        notice = mapping_notice.get(i["notice"], "")
        res_.append(
            {
                "设备名称": i["device_name"],
                "测点名称": i["sensor_name"],
                "标题": i["title"],
                "告警内容": i["content"],
                "告警等级": warn_level,
                "告警来源": warn_source,
                "处理状态": notice,
                "告警时间": i["create_time"].strftime("%Y-%m-%d %H:%M:%S"),
            }
        )
    df = pd.DataFrame(res_)

    script_dir = os.path.dirname(os.path.abspath(__name__))
    filePath = script_dir + "/static/upload/" + date
    if not os.path.exists(filePath):
        os.mkdir(filePath)
    filePath += f"/{filename}"
    df.to_excel(filePath, index=False)
    return send_file(filePath, as_attachment=True, attachment_filename=filename)


# [项目]北海，模板消息推送
def send_msg():
    try:
        o = {
            "code": "messageTemplate",
            "method": "POST",
            "data": {
                "code": "send",
                "attachs": [""],
                "pcUrl": "",
                "appUrl": "",
                "messageTemplateCode": "device_warn",
                "messageParam": {
                    "contentParam": {
                        "content": "测试12",
                        "title": "测试12",
                        "time": "测试12",
                    }
                },
            },
        }
        data = json.dumps(o)
        res = requests.post(
            "http://10.13.126.34:8091/sinoma-interface/api/common", data=data, timeout=5
        )
        if res.status_code != 200:
            print("请求失败", res.status_code)
            return
        print("模板消息推送", res.text)
    except Exception as e:
        print("模板消息推送异常", e)


# 以设备为单位展示告警
@warn.route("/getlist", methods=["post"])
def device_list():
    a = request.json
    b = parsing_permissions(request)
    scope_id = assign_value(a, "scope_id", 0)
    scoped = parsing_data_id(scope_id, request)
    device_name = assign_value(a, "device_name")
    warn_level = assign_value(a, "warn_level")
    start_time = assign_value(a, "start_time")
    end_time = assign_value(a, "end_time")
    sql = f"""SELECT d.id as device_id ,d.device_name,dt.name as device_type,max_warn_level,min_warn_level,warn_count,d.zone_id,
    d.factory_id,d.company_id,d.group_id,notice,start_time,end_time
    from device d
    LEFT JOIN device_type dt on dt.id=d.type_id
    LEFT JOIN (
    SELECT device_id,MIN(notice) as notice,count(id) as warn_count,MAX(warn_level) as max_warn_level,MIN(warn_level) as min_warn_level,
    MAX(pred_time) as end_time,MIN(create_time) as start_time
    FROM (SELECT w.* from warn w INNER JOIN sensor s on s.id = w.sensor_id) a where is_show=1 GROUP BY device_id 
    ) as w on w.device_id = d.id"""
    query1 = "where 1=1"
    if scoped != "":
        query1 += f" and {scoped}"
    if device_name != "":
        query1 += f" and device_name like '%{device_name}%' "
    if warn_level != "" and warn_level != "0":
        query1 += f" and max_warn_level = {warn_level}"
    if start_time != "":
        query1 += f" and start_time > '{start_time}'"
    if end_time != "":
        query1 += f" and end_time < '{end_time}' "
    sql_ = f"select * from ({sql}) as a {query1} and warn_count != 0 order by notice asc,end_time desc"
    sql_count = f"select count(device_id) from ({sql}) as a {query1} and warn_count != 0 and max_warn_level="
    res = db_pagelist(sql_, a["page"])

    num1 = db_fetchone(sql_count + "1")
    num2 = db_fetchone(sql_count + "2")
    num3 = db_fetchone(sql_count + "3")
    res["stat"] = {
        "level_1": num1["data"],
        "level_2": num2["data"],
        "level_3": num3["data"],
    }

    return res


# 查询设备下的告警
@warn.route("/details/list", methods=["post"])
def details_list():
    try:
        print("user:", get_url_user(request))

        a = request.json
        device_id = assign_value(a, "device_id")
        size = assign_value(a, "size", 10)
        page = assign_value(a, "page", 1)
        sql = f"""SELECT w.id,w.title,w.device_id,w.content,w.warn_source,w.warn_type,s.id as sensor_id,s.sensor_name,
        w.warn_level,w.create_time,IFNULL(w.pred_time,w.create_time) as pred_time,w.notice,w.is_show,w.zone_id,w.company_id,w.factory_id,w.group_id,
        w.cause,w.suggest,w.pred_data,w.standard_data
        from warn w
        right JOIN sensor s on w.sensor_id = s.id 
        where w.is_show = 1  and w.device_id ={device_id} order by w.pred_time desc """
        print("sql",sql)
        res = db_pagelist(sql, page, size)
        if res["data"]:
            colors = ["#6FCFF2", "#FEFF00", "#db0b99", "#FE0000"]
            suggestSpan = ["", "持续关注", "观察数据历史趋势", "请尽快现场排查"]
            for item in res["data"]:
                cause = item["cause"]
                suggest = item["suggest"]
                warn_level = item["warn_level"]
                item["suggestCause"] = {
                    "color": colors[warn_level],
                    "span": suggestSpan[warn_level],
                    "cause": cause,
                    "suggest": suggest,
                }
        return res
    except Exception as e:

        return utils.fail(data=[], msg=str(e))


@warn.route("/details/list/djc", methods=["post"])
def details_list1():
    a = request.json
    device_id = assign_value(a, "device_id")
    size = assign_value(a, "size", 99999)
    sql = f"""SELECT w.id,w.index,w.title,w.device_id,w.content,w.warn_source,s.id as sensor_id,s.sensor_name,
    w.warn_level,w.create_time,IFNULL(w.pred_time,w.create_time) as pred_time,w.notice,w.is_show,w.zone_id,w.company_id,w.factory_id,w.group_id,
    w.cause,w.suggest,w.pred_data
    from warn w
    right JOIN sensor s on w.sensor_id = s.id 
    where w.is_show = 1  and w.device_id ={device_id} order by w.pred_time desc """
    res = db_pagelist_djc(sql, a["page"], size)
    return res


# 删除设备下的告警
@warn.route("/device/delete", methods=["post"])
def ccc():
    a = request.json
    device_id = assign_value(a, "device_id")
    return db_update(
        "warn", {"is_show": 0}, f"WHERE device_id = %s", {"device_id": device_id}
    )


# 忽略告警
@warn.route("/ignore", methods=["get"])
def a():
    id_ = int(request.args.get("id"))
    db_update("warn", {"notice": 3})
    return db_update("warn", {"notice": 3}, f"WHERE id = %s", {"mid": id_})


@warn.route("/pull/list_1", methods=["post"])
def b():
    a = request.json
    device_id = assign_value(a, "device_id")
    sql = f"SELECT d.zone_id,d.factory_id,d.company_id,d.group_id from device d where d.id = {device_id}"
    res = db_list(sql)
    arr = [
        res["data"][0]["group_id"],
        res["data"][0]["company_id"],
        res["data"][0]["factory_id"],
        res["data"][0]["zone_id"],
    ]

    sql_ = f"""SELECT u.id,u.user_name,u.nick_name FROM userinfo u
    LEFT JOIN role r on u.role_id = r.id
    where r.dataId in  (0,{res['data'][0]["group_id"]},{res['data'][0]["company_id"]},{res['data'][0]["factory_id"]},{res['data'][0]["zone_id"]})"""
    res = db_list(sql_)
    return res


# 创建工单
@warn.route("add/notice", methods=["post"])
def c():
    a = request.json
    json_data = json.dumps(
        {
            "user_id": assign_value(a, "user_id"),
            "accept_id": assign_value(a, "accept_id"),
            "title": assign_value(a, "title"),
            "content": assign_value(a, "content"),
        }
    )  # 设置请求头  # 发送POST请求
    headers = {"Content-Type": "application/json"}
    try:
        requests.post("http://phm.demo.com/notice", data=json_data, headers=headers)
    except Exception as e:
        print(e)
    json_data1 = json.dumps(
        {
            "content": f"设备{assign_value(a, 'title')}工单待处理,产生原因为{assign_value(a, 'content')}",
            "device_id": assign_value(a, "device_id"),
            "group_id": assign_value(a, "group_id"),
            "company_id": assign_value(a, "company_id"),
            "factory_id": assign_value(a, "factory_id"),
            "zone_id": assign_value(a, "zone_id"),
            "sent_time": datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            "handle": 0,
            "type": "notice",
        }
    )  # 设置请求头  # 发送POST请求
    try:
        ip = current_app.config['settings']['webapi-ip']
        host = current_app.config['settings']['webapi-host']
        requests.post(f"http://{ip}:{host}/api/mqtt/add", data=json_data1, headers=headers)
    except Exception as e:
        print(e)
    keys_not_add = ["id", "start_time"]
    addModel = {
        key: request.json[key] for key in request.json if key not in keys_not_add
    }
    device_id = assign_value(request.json, "device_id")
    db_update("warn", {"notice": 1}, f"where device_id = %s", {"device_id": device_id})

    addModel["status"] = 2
    return db_insert("notice", addModel)


@warn.route("ignore_", methods=["post"])
def d():
    device_id = assign_value(request.json, "device_id")
    return db_update(
        "warn", {"notice": 3}, f"where device_id = %s", {"device_id": device_id}
    )
